import sys
import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime

# 1. 设备列表
device_list = ['GW42B3']
placeholders = ','.join(f"'{i}'" for i in device_list)

# 2. 连接数据库
engine = create_engine("mysql+pymysql://tester:tester1234@192.168.3.167:3306/gwza231dev?charset=utf8mb4")

# 3. 读取数据
query = f"""
SELECT  device_id,
        update_time AS time,
        JSON_UNQUOTE(JSON_EXTRACT(memo, '$.pa')) AS pa
FROM    map_device_his1009
WHERE   device_id IN ({placeholders})
AND     update_time BETWEEN '2025-10-09 13:00:00' AND '2025-10-09 14:00:00'
ORDER BY device_id, update_time
"""

df = pd.read_sql(query, engine)

# 4. 清洗脏数据
df['pa'] = df['pa'].astype(str).str.replace(r'(\d+)\.\d+\.', r'\1.', regex=True)
df['pa'] = pd.to_numeric(df['pa'], errors='coerce') - 100000  #其他减去100000  可自己配置
df = df.dropna(subset=['pa'])
# 5. 配置时间0：只有相同时间才会被匹配到 ；>0：按照时间间隔，秒为单位，比如10s那就10s内都定义成相同时间 ； -1：将不同的时间用上一个值补齐 ；-2：原数据
time_threshold = -1

if time_threshold>0:
    # 6. 将时间列转换为时间戳
    df['time'] = pd.to_datetime(df['time'])
    df['timestamp'] = df['time'].astype('int64') // 10 ** 9  # 转换为秒级时间戳

    # 7. 对时间戳进行分组
    df['group'] = df['timestamp'].diff().fillna(0).cumsum().apply(lambda x: int(x // time_threshold))

    # 8. 为每个分组分配一个统一的时间（这里选择每个分组的最小时间）
    grouped = df.groupby('group')
    df['time'] = grouped['time'].transform('min')

    # 9. 去掉时间戳和分组列
    df = df.drop(columns=['timestamp', 'group'])

    # 10. 确保 'pa' 列是数值类型
    df['pa'] = pd.to_numeric(df['pa'], errors='coerce')
    df = df.dropna(subset=['pa'])
    # 11. 透视：行=time，列=device_id，值=pa
    pivot = df.pivot_table(index='time', columns='device_id', values='pa', aggfunc='mean')
    # 12. 仅保留所有设备都有值的时间点
    pivot = pivot.dropna()
elif time_threshold==-1:
    # 5. 聚合重复记录，取平均值
    df = df.groupby(['device_id', 'time']).mean().reset_index()

    # 6. 透视：行=time，列=device_id，值=pa
    pivot = df.pivot(index='time', columns='device_id', values='pa')

    # 7. 前向填充缺失值
    pivot = pivot.ffill()

    # 8. 获取所有唯一的时间点
    all_times = pd.to_datetime(pivot.index).unique()

    # 9. 重索引，确保所有时间点都有记录，缺失的用NaN填充
    pivot = pivot.reindex(all_times)

    # 10. 将pa值取整，先替换NaN为0，再转换为整数
    pivot = pivot.fillna(0).astype(int)
    # 12. 仅保留所有设备都有值的时间点
    pivot = pivot.dropna()
elif time_threshold==0:
    # 5. 透视：行=time，列=device_id，值=pa
    pivot = df.pivot_table(index='time', columns='device_id', values='pa')
    # 12. 仅保留所有设备都有值的时间点
    pivot = pivot.dropna()
elif time_threshold==-2:
    df['dev_seq'] = df.groupby('device_id').cumcount()  # 0,1,2… 每个设备内部序号

    # 4. 透视 → 行号是第 n 条记录，列是 device_id_time / device_id_pa
    p_time = df.pivot(index='dev_seq', columns='device_id', values='time')
    p_pa = df.pivot(index='dev_seq', columns='device_id', values='pa')

    # 5. 把 time、pa 交替合并
    pivot = pd.DataFrame()
    for col in p_time.columns:
        pivot[f'{col}_time'] = p_time[col]
        pivot[f'{col}_pa'] = p_pa[col]
else:
    print("输入了错误的 time_threshold")
    sys.exit()

# 13. 写入 Excel
current_time = datetime.now().strftime("%Y%m%d_%H%M%S")
file_name = f'pa_{time_threshold}_{current_time}.xlsx'
pivot.to_excel(file_name, index=True)

print('已生成')